package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Oms_order_item16 {
	//插入
	@Test
	public void insert() {
		Connection conn=null;
		//准备数据库用户名，密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			Class.forName(driver);//这是属于反射的内容
			//获取连接java.sq.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令receiver_name
		String sql="insert into Oms_order_item(id,order_id,order_sn,product_id,product_pic)values(1,2,3,4,5)";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	//修改
	@Test
	public void update() {
		Connection conn=null;
		//准备数据库用户名，密码，url,driver
		String  username ="root";
		String password ="root";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		String drive="com.mysql.jdbc.Driver";
		//加载驱动类到虚拟机中
		try {
			Class.forName(drive);//这是属于反射的内容
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="update Oms_order_item set product_pic='不好吧' where id=1";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test
	public void delete() {
		Connection conn=null;
		//准备数据库用户名，密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEnconding=utf-8";
		try {
			Class.forName(driver);
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="delete from Oms_order_item where id =2";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"删除成功":"删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	@Test
	public void selet() {
		Connection conn=null;
		//准备数据库用户名，密码，url,driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这属于反射的内容
			//获取数据库连接java.sql.connection
			conn=DriverManager.getConnection(url,username,password);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="select * from Oms_order_item";
		Statement st=null;
		ResultSet rs = null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st = conn.createStatement();
			//执行sql命令
			rs = st.executeQuery(sql);
			//处理查询结果集
			while(rs.next()) {
				//System.out.println(rs.getInt(1));id,order_id,order_sn,product_id,product_pic
				//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getObject("order_id"));
				System.out.println(rs.getObject("order_sn"));
				System.out.println(rs.getObject("product_id"));
				System.out.println(rs.getObject("product_pic"));
				//获取username列
				//System.out.println(rs.getString("username"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
}
	}


}
